package com.etl.jdbc.jdbc;


import com.alibaba.fastjson.JSONObject;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 *  JDBC Sql语句工具类
 *
 * User: Locker1995
 * Date: 2018-03-29
 * Time: 9:16 AM
 */
public class SqlUtil {

    /**
     *
     * 获得批量插入的 Sql语句
     *
     * @param tableName
     * @param columns
     * @return
     */
    public static String getInsertSql(String tableName,List<JSONObject> columns){
        StringBuffer sql = new StringBuffer();
        //insert into tableName(
        sql.append("insert into ").append(tableName).append("(");
        for(int i=0;i<columns.size();i++){
            String column = columns.get(i).getString("ColumnName");
            sql.append(column);
            if(i+1<columns.size())
                sql.append(",");
        }
        sql.append(" ) values(");
        for(int i=0;i<columns.size();i++){
            sql.append(" ? ");
            if(i+1<columns.size())
                sql.append(",");
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     *
     *
     *
     * @param databaseType
     * @param databaseName
     * @return
     */
    public static String getSchemaTableNamesSql(String databaseType,String databaseName){


        String sql="";

        if("mysql".equals(databaseType.toLowerCase()))
            sql = "select table_name as tableName from information_schema.tables " +
                    "where table_schema='"+databaseName+"' and table_type='base table'";
        // table_type 还有view 等其他
        if("sqlserver".equals(databaseType.toLowerCase()))
            sql="SELECT name as tableName FROM sysobjects where xtype='U' ORDER BY name";
        if("oracle".equals(databaseType.toLowerCase()))
            sql="select table_name as tableName from user_tables";

        return sql;
    }


    public static String getSchemaTableColumnsRelationSql(String databaseType,String tableName,String schemaName){
        
        StringBuffer sb = new StringBuffer();
        

        if("mysql".equals(databaseType.toLowerCase()))
            sb.append("select " +
                    "c.column_name as 'field_name' ," +
                    "c.data_type as 'data_type'," +
                    "c.column_type as 'column_type'," +
                    "c.column_key as 'key'," +
                    "k.referenced_table_schema as 'foreign_key_schema'," +
                    "k.REFERENCED_TABLE_NAME as 'foreign_key_table',"+
                    "k.referenced_column_name as 'foreign_key_column'" +
                    " from INFORMATION_SCHEMA.columns c " +
                    " left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE k" +
                    " on c.column_name = k.column_name " +
                    " and c.table_schema = k.table_schema " +
                    " and c.table_name = k.table_name " +
                    " and k.referenced_table_schema is not NULL " +
                    " where c.table_name= '"+tableName+"' and c.table_schema= '"+schemaName+"'");
        if("sqlserver".equals(databaseType.toLowerCase()))
           sb.append("select\n" +
                   "c.name as 'field_name' ,\n" +
                   "TYPE_NAME(c.xtype)+'('+CONVERT(varchar(3),c.length)+')' as 'column_type',\n" +
                   "TYPE_NAME(c.xtype) AS 'data_type',\n" +
                   "case when exists(" +
                   "    SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in " +
                   "        ( SELECT name FROM sysindexes WHERE indid in " +
                   "            ( SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) " +
                   "                then 'PRI' else '' end as 'key' , \n" +
                   "(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid) as 'foreign_key_column' ,\n" +
                   "object_name(b.rkeyid) as 'foreign_key_table' \n" +
                   "from syscolumns c \n" +
                   "join sysobjects o \n" +
                   "on o.id = c.id \n" +
                   "LEFT join sysforeignkeys b \n" +
                   "on b.fkeyid = c.id \n" +
                   "and b.fkey = c.colid  \n" +
                   "where o.name='"+tableName+"'");
        if("oracle".equals(databaseType.toLowerCase()))
            sb.append("select \n" +
                    "c.column_name as field_name,\n" +
                    "\"CONCAT\"(c.DATA_TYPE, \"CONCAT\"('(', \"CONCAT\"(\"TO_CHAR\"(c.CHAR_LENGTH), ')'))) as column_type ,\n" +
                    "c.DATA_TYPE AS data_type,\n" +
                    "case when (\n" +
                    "select count(*) from user_cons_columns c1 LEFT JOIN user_constraints b on b.constraint_name = C1.constraint_name\n" +
                    "where C1.column_name = c.column_name and c1.table_name = c.table_name and constraint_type = 'P'\n" +
                    ") = 1 then 'PRI' else '' end as key ,\n" +
                    "(\n" +
                    "select \n" +
                    "ucc2.column_name\n" +
                    "from user_cons_columns ucc \n" +
                    "join user_constraints uc \n" +
                    "on uc.constraint_name = ucc.constraint_name\n" +
                    "join user_cons_columns ucc2 \n" +
                    "on uc.r_constraint_name = ucc2.constraint_name\n" +
                    "where ucc.table_name = c.table_name and UC.constraint_type='R' and c.column_name = UCC.column_name\n" +
                    ") as foreign_key_column,\n" +
                    "(\n" +
                    "select \n" +
                    "ucc2.table_name\n" +
                    "from user_cons_columns ucc \n" +
                    "join user_constraints uc \n" +
                    "on uc.constraint_name = ucc.constraint_name\n" +
                    "join user_cons_columns ucc2 \n" +
                    "on uc.r_constraint_name = ucc2.constraint_name\n" +
                    "where ucc.table_name = c.table_name and UC.constraint_type='R' and c.column_name = UCC.column_name\n" +
                    ") as foreign_key_table\n" +
                    "from user_tab_columns c\n" +
                    "where c.TABLE_NAME = '").append(tableName).append("'");


        return  sb.toString();
        
    }

}
